﻿using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.Logging;
using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Fast.Extensions.DatabaseAccessor.EntityFrameworkCore
{
    /// <summary>
    /// ADO.NET 扩展类
    /// </summary>
    [SkipScan]
    public static class AdoNetExtensions
    {
        /// <summary>
        /// 执行 Sql 返回 DataTable
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="dbParameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">行为</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteReader(this DatabaseFacade databaseFacade, string sql, DbParameter[] dbParameters = null, CommandType commandType = CommandType.Text, CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            //构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, dbParameters, commandType);

            // 读取数据
            using var dbDataReader = dbCommand.ExecuteReader(commandBehavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReader));

            // 填充到 DataTable
            var dataTable = dbDataReader.ToDataTable();

            // 释放命令对象
            dbCommand.Dispose();

            return dataTable;
        }
        /// <summary>
        /// 执行 Sql 返回 DataTable
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">行为</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteReader(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            //构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, model, commandType);

            // 读取数据
            using var dbDataReader = dbCommand.ExecuteReader(commandBehavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReader));

            // 填充到 DataTable
            var dataTable = dbDataReader.ToDataTable();

            // 释放命令对象
            dbCommand.Dispose();


            return dataTable;
        }
        /// <summary>
        /// 执行 Sql 返回 DataTable
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="dbParameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">行为</param>
        /// <returns>DataTable</returns>
        public static async Task<DataTable> ExecuteReaderAsync(this DatabaseFacade databaseFacade, string sql, DbParameter[] dbParameters = null, CommandType commandType = CommandType.Text, CommandBehavior commandBehavior = CommandBehavior.Default, CancellationToken cancellationToken = default)
        {
            //构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, dbParameters, commandType, cancellationToken);

            // 读取数据
            using var dbDataReader = await dbCommand.ExecuteReaderAsync(commandBehavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReaderAsync));

            // 填充到 DataTable
            var dataTable = dbDataReader.ToDataTable();

            // 释放命令对象
            dbCommand.Dispose();

            return dataTable;
        }
        /// <summary>
        /// 执行 Sql 返回 DataTable
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">行为</param>
        /// <returns>DataTable</returns>
        public static async Task<DataTable> ExecuteReaderAsync(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CommandBehavior commandBehavior = CommandBehavior.Default, CancellationToken cancellationToken = default)
        {
            //构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, model, commandType, cancellationToken);

            // 读取数据
            using var dbDataReader = await dbCommand.ExecuteReaderAsync(commandBehavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReaderAsync));

            // 填充到 DataTable
            var dataTable = dbDataReader.ToDataTable();

            // 释放命令对象
            dbCommand.Dispose();

            return dataTable;
        }
        /// <summary>
        /// 执行 Sql 语句返回受影响行数
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text)
        {
            //构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, parameters, commandType);

            // 执行返回受影响行数
            var rows = dbCommand.ExecuteNonQuery();

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteNonQuery));

            // 释放命令对象
            dbCommand.Dispose();

            return rows;
        }
        /// <summary>
        /// 执行 Sql 语句返回受影响行数
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">参数模型</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteNonQuery(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text)
        {
            //构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, model, commandType);

            // 执行返回受影响行数
            var rows = dbCommand.ExecuteNonQuery();

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteNonQuery));

            // 释放命令对象
            dbCommand.Dispose();

            return rows;
        }
        /// <summary>
        /// 执行 Sql 语句返回受影响行数
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>受影响行数</returns>
        public static async Task<int> ExecuteNonQueryAsync(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            //构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, parameters, commandType, cancellationToken);

            // 执行返回受影响行数
            var rows = await dbCommand.ExecuteNonQueryAsync(cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteNonQueryAsync));

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return rows;
        }
        /// <summary>
        /// 执行 Sql 语句返回受影响行数
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>受影响行数</returns>
        public static async Task<int> ExecuteNonQueryAsync(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            //构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, model, commandType, cancellationToken);

            // 执行返回受影响行数
            var rows = await dbCommand.ExecuteNonQueryAsync(cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteNonQueryAsync));

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return rows;
        }
        /// <summary>
        /// 执行 Sql 返回单行单列的值
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>单行单列的值</returns>
        public static object ExecuteScalar(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text)
        {
            // 构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, parameters, commandType);

            // 执行返回单行单列的值
            var result = dbCommand.ExecuteScalar();

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteScalar));

            // 释放命令对象
            dbCommand.Dispose();

            return result != DBNull.Value ? result : default;
        }
        /// <summary>
        /// 执行 Sql 返回单行单列的值
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>单行单列的值</returns>
        public static object ExecuteScalar(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text)
        {
            // 构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, model, commandType);

            // 执行返回单行单列的值
            var result = dbCommand.ExecuteScalar();

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteScalar));

            // 释放命令对象
            dbCommand.Dispose();

            return result != DBNull.Value ? result : default;
        }
        /// <summary>
        /// 执行 Sql 返回单行单列的值
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>单行单列的值</returns>
        public static async Task<object> ExecuteScalarAsync(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            // 构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, parameters, commandType, cancellationToken);

            // 执行返回单行单列的值
            var result = await dbCommand.ExecuteScalarAsync(cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteScalarAsync));

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return result != DBNull.Value ? result : default;
        }
        /// <summary>
        /// 执行 Sql 返回单行单列的值
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>单行单列的值</returns>
        public static async Task<object> ExecuteScalarAsync(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)
        {
            // 构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, model, commandType, cancellationToken);

            // 执行返回单行单列的值
            var result = await dbCommand.ExecuteScalarAsync(cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteScalarAsync));

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return result != DBNull.Value ? result : default;
        }
        /// <summary>
        /// 执行 Sql 返回 DataSet
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="behavior">行为</param>
        /// <returns>DataSet</returns>
        public static DataSet DataAdapterFill(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text, CommandBehavior behavior = CommandBehavior.Default)
        {
            // 构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, parameters, commandType);

            // 读取数据
            using var dbDataReader = dbCommand.ExecuteReader(behavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReader));

            // 填充到 DataSet
            var dataSet = dbDataReader.ToDataSet();

            // 释放命令对象
            dbCommand.Dispose();

            return dataSet;
        }
        /// <summary>
        /// 执行 Sql 返回 DataSet
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="model">命令模型</param>
        /// <param name="behavior">行为</param>
        /// <returns>DataSet</returns>
        public static DataSet DataAdapterFill(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CommandBehavior behavior = CommandBehavior.Default)
        {
            // 构建数据库命令对象
            var dbCommand = databaseFacade.BuildDbCommand(sql, model, commandType);

            // 读取数据
            using var dbDataReader = dbCommand.ExecuteReader(behavior);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReader));

            // 填充到 DataSet
            var dataSet = dbDataReader.ToDataSet();

            // 释放命令对象
            dbCommand.Dispose();

            return dataSet;
        }
        /// <summary>
        /// 执行 Sql 返回 DataSet
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">命令参数</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="behavior">行为</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>DataSet</returns>
        public static async Task<DataSet> DataAdapterFillAsync(this DatabaseFacade databaseFacade, string sql, DbParameter[] parameters = null, CommandType commandType = CommandType.Text, CommandBehavior behavior = CommandBehavior.Default, CancellationToken cancellationToken = default)
        {
            // 构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, parameters, commandType, cancellationToken);

            // 读取数据
            using var dbDataReader = await dbCommand.ExecuteReaderAsync(behavior, cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReaderAsync));

            // 填充到 DataSet
            var dataSet = dbDataReader.ToDataSet();

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return dataSet;
        }
        /// <summary>
        /// 执行 Sql 返回 DataSet
        /// </summary>
        /// <param name="databaseFacade">ADO.NET 数据库对象</param>
        /// <param name="sql">sql 语句</param>
        /// <param name="model">命令模型</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="behavior">行为</param>
        /// <param name="cancellationToken">异步取消令牌</param>
        /// <returns>DataSet</returns>
        public static async Task<DataSet> DataAdapterFillAsync(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CommandBehavior behavior = CommandBehavior.Default, CancellationToken cancellationToken = default)
        {
            // 构建数据库命令对象
            var dbCommand = await databaseFacade.BuildDbCommandAsync(sql, model, commandType, cancellationToken);

            // 读取数据
            using var dbDataReader = await dbCommand.ExecuteReaderAsync(behavior, cancellationToken);

            //记录 Sql 执行命令日志
            LogSqlExecuteCommand(databaseFacade, dbCommand, nameof(dbCommand.ExecuteReaderAsync));

            // 填充到 DataSet
            var dataSet = dbDataReader.ToDataSet();

            // 释放命令对象
            await dbCommand.DisposeAsync();

            return dataSet;
        }

        /// <summary>
        /// 输出原始 Sql 执行日志（ADO.NET）
        /// </summary>
        /// <param name="databaseFacade"></param>
        /// <param name="dbCommand"></param>
        /// <param name="actionName">执行动作</param>
        private static void LogSqlExecuteCommand(DatabaseFacade databaseFacade, DbCommand dbCommand, string actionName)
        {
            // 打印执行 SQL
            App.PrintToMiniProfiler(MiniProfilerCategory.Sql.GetDescription(), actionName, dbCommand.CommandText);

            // 构建日志内容
            var sqlLogBuilder = new StringBuilder();
            sqlLogBuilder.Append($@"Executed DbCommand {actionName} ");
            sqlLogBuilder.Append(@" [Parameters=[");

            // 拼接命令参数
            var parameters = dbCommand.Parameters;
            for (var i = 0; i < parameters.Count; i++)
            {
                var parameter = parameters[i];
                var parameterType = parameter.GetType();

                // 处理 OracleParameter 参数打印
                var dbType = parameterType.FullName.Equals("Oracle.ManagedDataAccess.Client.OracleParameter", StringComparison.OrdinalIgnoreCase)
                    ? parameterType.GetProperty("OracleDbType").GetValue(parameter) : parameter.DbType;

                sqlLogBuilder.Append($"{parameter.ParameterName}='{parameter.Value}' (Size = {parameter.Size}) (DbType = {dbType})");
                if (i < parameters.Count - 1) sqlLogBuilder.Append(", ");
            }

            sqlLogBuilder.Append(@$"], CommandType='{dbCommand.CommandType}', CommandTimeout='{dbCommand.CommandTimeout}']");
            sqlLogBuilder.Append("\r\n");
            sqlLogBuilder.Append(dbCommand.CommandType == CommandType.StoredProcedure ? "EXEC " + dbCommand.CommandText : dbCommand.CommandText);

            // 打印日志
            var logger = databaseFacade.GetService<ILogger<SqlExecuteCommand>>();
            logger.LogInformation(sqlLogBuilder.ToString());
        }
    }
}
